package com.iconflux.training;
import java.io.*;
import java.sql.*;
import java.util.*;

public class Company_Database {

	/**
	 * @param args
	 */
	public static Connection cn=null;
	public static PreparedStatement pst=null;
	public static ResultSet rs=null;
	public static BufferedReader br=new BufferedReader(new InputStreamReader(System.in));

	public static void insert()
		{
			try
			{
			int id=0;
					Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
					cn=DriverManager.getConnection("jdbc:odbc:emp");
					
					String str="select max(empid) from tblemployee";
					pst=cn.prepareStatement(str);
					rs=pst.executeQuery();
					
					while(rs.next())
					{
						id=rs.getInt(1);
					}
					
					id++;
					
					
					System.out.print("\t\tEnter the Name=");
					String name=br.readLine();
					
					System.out.print("\t\tEnter the Designation=");
					String desig=br.readLine();
					
					System.out.print("\t\tEnter the Company Name=");
					String cmp=br.readLine();
					
					String st="insert into tblemployee values(?,?,?,?)";					
					pst=cn.prepareStatement(st);
					pst.setInt(1,id);
					pst.setString(2,name);
					pst.setString(3,desig);
					pst.setString(4,cmp);
					pst.executeUpdate();
					pst.close();
					rs.close();
					cn.close();
			
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}

		
		public static void delete()
		{
			try
			{
				Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
				cn=DriverManager.getConnection("jdbc:odbc:emp");
				
				System.out.print("\t Enter the Id you Want To Delete=");
				int id=Integer.parseInt(br.readLine());
					
				String str="select empid from tblemployee";
				pst=cn.prepareStatement(str);
				rs=pst.executeQuery();
				
				while(rs.next())
				{
						if(id==rs.getInt(1))
						{
							String strd="delete from tblemployee where empid=?";
							pst=cn.prepareStatement(strd);
							pst.setInt(1,id);
							pst.executeUpdate();
							
						}
						
				}
				
					
			
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		
		public static void update()
		{
			try
			{
				Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
				cn=DriverManager.getConnection("jdbc:odbc:emp");
				
				System.out.print("\t Enter the Id you Want To Delete=");
				int id=Integer.parseInt(br.readLine());
					
				String str="select empid from tblemployee";
				pst=cn.prepareStatement(str);
				rs=pst.executeQuery();
				
				while(rs.next())
				{
						if(id==rs.getInt(1))
						{
							String strd="update tblemployee set emp_name=?,emp_designation=?,emp_company=? where empid=?";
							pst=cn.prepareStatement(strd);
							
							
							System.out.print("\t\tEnter the Name=");
					String name=br.readLine();
					
					System.out.print("\t\tEnter the Designation=");
					String desig=br.readLine();
					
					System.out.print("\t\tEnter the Company Name=");
					String cmp=br.readLine();
							pst.setString(1,name);
							pst.setString(2,desig);
							pst.setString(3,cmp);
							pst.setInt(4,id);
							pst.executeUpdate();
							
						}
				}
			
			
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		
		public static void disp()
		{
			try
			{
			
			
					Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
					cn=DriverManager.getConnection("jdbc:odbc:emp");
					
					String str="select * from tblemployee";
					pst=cn.prepareStatement(str);
					rs=pst.executeQuery();
					
					while(rs.next())
					{
						System.out.print("\t " + rs.getInt(1));
						System.out.print("\t " + rs.getString(2));
						System.out.print("\t " + rs.getString(3));
						System.out.print("\t " + rs.getString(4));
						System.out.println();
					}
					
					rs.close();
					pst.close();
					cn.close();
			
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		int ch;
		try{
		while(true)
		{
			System.out.println("1. Insert.");
			System.out.println("2. Display.");
			System.out.println("3. Update.");
			System.out.println("4. Delete.");
			System.out.println("5. Exit.");
			
			System.out.print("Please select the Choice==");
			
			 BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
	            ch=Integer.parseInt(br.readLine());
			
			switch(ch)
			{
				case 1:
						insert();
						break;
				case 2:
						disp();
						break;
				case 3:
						update();
						break;
				case 4:
						delete();
						break;
				case 5:
						System.exit(0);
						break;
				default:
						System.out.println("Wrong Choice");
			}
		}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		} 
		
		
	}

	}


